package com.todd.studentms.servlet;

import cn.hutool.json.JSONObject;
import com.todd.studentms.entity.Course;
import com.todd.studentms.entity.Major;
import com.todd.studentms.util.Tdber;

import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@WebServlet("/echarts/*")
public class echartsServlet extends EssentialServlet {
    // 获取各个专业的人数的信息
    // /majordata
    public List<Map<String, Object>> majordata(JSONObject jsonObj, HttpServletRequest req, HttpServletResponse res) {
        return Tdber.runQuery("select m.major_name majorName ,count(*) count\n" +
                "from person p join class cls on p.class_id=cls.id join major m on m.id = cls.major_id\n" +
                "group by m.id,m.major_name\n", null);
    }

    // 各个专业每年的招生情况
    // /majorrecruitmentdata
    public Map<String, Object> majorrecruitmentdata(JSONObject jsonObj, HttpServletRequest req, HttpServletResponse res) {
        Map<String, Object> result = new HashMap<>();
        // 有哪些"年"的信息
        List<Integer> allYears = Tdber
                .runQuery("select distinct enrollment_year from person where person_type_id='persontype003' ORDER BY enrollment_year;", null)
                .stream()
                .map(map -> (Integer) map.get("enrollment_year"))
                .collect(Collectors.toList());
        // 所有的majorId
        List<String> allMajorId = Tdber.query(new Major()).stream().map(Major::getId).collect(Collectors.toList());
        // 分组后的所有专业每年的招生信息
        List<Map<String, Object>> groupedMajorRecruitmentData = Tdber
                .runQuery("select m.id majorId,m.major_name majorName, p.enrollment_year enrollmentYear,count(*) count\n" +
                        "from person p join class cls on cls.id=p.class_id join major m on m.id=cls.major_id\n" +
                        "where p.person_type_id='persontype003'\n" +
                        "group by m.id,p.enrollment_year\n" +
                        "order by m.id, p.enrollment_year asc;", null);
        // 将信息转换为serise格式
        List<Map<String, Object>> serise = new ArrayList<>();
        allMajorId.forEach(majorId -> {
            Map<String, Object> se = new HashMap<>();
            // 当前专业的信息
            List<Map<String, Object>> groupedMajorRecruitmentDataOfTheMajor = groupedMajorRecruitmentData
                    .stream()
                    .filter(d -> majorId.equals(d.get("majorId")))
                    .collect(Collectors.toList());
            se.put("name", groupedMajorRecruitmentDataOfTheMajor.get(0).get("majorName"));
            se.put("type", "line");
            se.put("smooth", true);
            se.put("data", groupedMajorRecruitmentDataOfTheMajor.stream().map(m -> (Long) m.get("count")).toArray());
            serise.add(se);
        });
        result.put("years", allYears);
        result.put("series", serise);
        return result;
    }

    // 各个课程的平均分,最高分, 最低分等信息
    public Map<String, Object> courseaverages(JSONObject jsonObj, HttpServletRequest req, HttpServletResponse res) {
        Map<String, Object> result = new HashMap<>();
        List<Map<String, Object>> courseScores = Tdber.runQuery("select c.course_name courseName,\n" +
                "\t\t\t cast(avg(score) as  DECIMAL(10,2)) avgScore,\n" +
                "\t\t\t cast(max(score) as  DECIMAL(10,2)) maxScore,\n" +
                "\t\t\t cast(min(score) as  DECIMAL(10,2)) minScore\n" +
                "from grade g join course c on g.course_id=c.id\n" +
                "group by g.course_id\n" +
                "order by c.id asc;\n", null)
                .stream()
                .collect(Collectors.toList());
        result.put("courseNames", courseScores
                .stream()
                .map(ca -> (String) ca.get("courseName"))
                .toArray());
        result.put("averages", courseScores
                .stream()
                .map(m -> (BigDecimal) m.get("avgScore"))
                .toArray()
        );
        result.put("maxs", courseScores
                .stream()
                .map(m -> (BigDecimal) m.get("maxScore"))
                .toArray()
        );
        result.put("mins", courseScores
                .stream()
                .map(m -> (BigDecimal) m.get("minScore"))
                .toArray()
        );
        return result;
    }
}
